package org.example.curriculum_design_6_16.mapper;

import org.apache.ibatis.annotations.*;
import org.example.curriculum_design_6_16.entity.pojo.Grade;
import com.baomidou.mybatisplus.core.mapper.BaseMapper;

import java.util.List;

/**
 * @author 郑凯
 * @description 针对表【grade】的数据库操作Mapper
 * @createDate 2025-06-16 09:25:48
 * @Entity org.example.curriculum_design_6_16.entity.pojo.Grade
 */
@Mapper
public interface GradeMapper extends BaseMapper<Grade> {

    /**
     * 根据学生ID查询成绩列表
     * @param studentId 学生ID
     * @return 成绩列表
     */
    @Select("SELECT * FROM grade WHERE student_id = #{studentId} ORDER BY score DESC")
    List<Grade> selectByStudentId(@Param("studentId") String studentId);

    @Select("SELECT * FROM grade WHERE student_id = #{studentId} AND semester = 2 ORDER BY score DESC")
    List<Grade> getScoreEFF(@Param("studentId") String studentId);

    /**
     * 根据课程ID查询成绩列表
     * @param courseId 课程ID
     * @return 成绩列表
     */
    @Select("SELECT * FROM grade WHERE course_id = #{courseId} ORDER BY score DESC")
    List<Grade> selectByCourseId(@Param("courseId") String courseId);

    /**
     * 根据学生ID删除成绩记录
     * @param studentId 学生ID
     * @return 影响的行数
     */
    @Delete("DELETE FROM grade WHERE student_id = #{studentId}")
    int deleteByStudentId(@Param("studentId") String studentId);

    /**
     * 根据学生ID和课程ID查询成绩
     * @param studentId 学生ID
     * @param courseId 课程ID
     * @return 成绩列表
     */
    @Select("SELECT * FROM grade WHERE student_id = #{studentId} AND course_id = #{courseId}")
    Grade selectByStudentAndCourse(
            @Param("studentId") String studentId,
            @Param("courseId") String courseId);

    /**
     * 计算某门课程的平均分
     * @param courseId 课程ID
     * @return 平均分
     */
    @Select("SELECT AVG(grade_value) FROM grade WHERE course_id = #{courseId}")
    Double selectAverageGradeByCourse(@Param("courseId") String courseId);

    /**
     * 更新成绩备注信息
     * @param gradeId 成绩ID
     * @param remark 备注内容
     * @return 影响的行数
     */
    @Update("UPDATE grade SET remark = #{remark} WHERE id = #{gradeId}")
    int updateRemark(
            @Param("gradeId") Integer gradeId,
            @Param("remark") String remark);

    /**
     * 检查学生是否已有某门课程的成绩
     * @param studentId 学生ID
     * @param courseId 课程ID
     * @return 是否存在
     */
    @Select("SELECT COUNT(*) FROM grade WHERE student_id = #{studentId} AND course_id = #{courseId}")
    boolean existsByStudentAndCourse(
            @Param("studentId") String studentId,
            @Param("courseId") String courseId);
}